# 数据处理 #
import datetime

import pandas as pd
import utils.utils
from app import app
from db.db import db
from db.models import (DriversServiceTable, DriverTable, GPSTable, OrderTable,
                       SimulationTable, StatesTable, TDTable, TimesTable)


@app.route('/update/id_state/<order_tablename>')
def update_orders_states(order_tablename):
    """
    功能：订单数据的状态匹配
    """
    OrderTable.__tablename__ = f'{order_tablename}'
    orders = OrderTable.query.all()
    for i in range(len(orders)):
        # 起点状态
        sql = f'''
        select "gid" from
        (
            select "gid",st_within(temptable1."geom",state."geom") from
            (
                select "geom_start" as geom from {order_tablename}
                    where "id" = '{orders[i].id}'
            ) as temptable1, state
        ) as temptable2
        where "st_within" = 't';
        '''
        id_state_start = db.session.execute(sql).fetchone()
        if id_state_start:
            orders[i].id_state_start = id_state_start[0]
        # 终点状态
        sql = f'''
        select "gid" from
        (
            select "gid",st_within(temptable1."geom",state."geom") from
            (
                select "geom_end" as geom from {order_tablename}
                    where "id" = '{orders[i].id}'
            ) as temptable1, state
        ) as temptable2
        where "st_within" = 't';
        '''
        id_state_end = db.session.execute(sql).fetchone()
        if id_state_end:
            orders[i].id_state_end = id_state_end[0]
        print(i + 1, '/', len(orders))
    db.session.commit()
    return '状态匹配完成！'


@app.route('/update/state_mapping/<order_tablename>')
def state_mapping(order_tablename):
    """
    功能：订单数据的状态匹配
    备注：面包含点
    """
    states = StatesTable.query.all()
    for i in range(len(states)):
        sql = f'''
        select "id" from
        (
            select "id",st_contains(temptable1."geom",{order_tablename}."geom_start") from
            (
                select "geom" from state where "gid" = {states[i].gid}
            ) as temptable1,{order_tablename}
        )as temptable2
        where "st_contains" = 't';
        '''
        ids_order = db.session.execute(sql).fetchall()
        for id_order in ids_order:
            order = OrderTable.query.filter_by(id = id_order[0]).first()
            if order:
                order.id_state_start = states[i].gid
        sql = f'''
        select "id" from
        (
            select "id",st_contains(temptable1."geom",{order_tablename}."geom_end") from
            (
                select "geom" from state where "gid" = {states[i].gid}
            ) as temptable1,{order_tablename}
        )as temptable2
        where "st_contains" = 't';
        '''
        ids_order = db.session.execute(sql).fetchall()
        for id_order in ids_order:
            order = OrderTable.query.filter_by(id = id_order[0]).first()
            if order:
                order.id_state_end = states[i].gid
        db.session.commit()
        print(i+1,'/',len(states))
    return '订单数据的状态匹配，成功！'


@app.route('/insert/times')
def insert_times():
    """
    功能：时间表插入数据
    """
    times_start = pd.timedelta_range(start='00:00:00',
                                     end='24:00:00',
                                     freq='0.5H')
    times_end = pd.timedelta_range(start='00:29:59',
                                   end='23:59:59',
                                   freq='0.5H')
    count = len(times_start)
    for i in range(count - 1):
        time_start = str(times_start[i])
        time_start = time_start[7:]  # 取出数据
        time_end = str(times_end[i])
        time_end = time_end[7:]  # 取出数据
        objTime = TimesTable(i + 1, time_start, time_end)
        db.session.add(objTime)
    db.session.commit()
    return 'times表插入数据成功'


@app.route('/insert/drivers_service')
def insert_drivers_service():
    """
    功能：司机服务表插入数据
    """
    i = 1  # 进度
    # 获取所有时间段
    sql = ''' select * from times order by "id"; '''
    df_times = pd.read_sql(sql, db.engine, index_col='id')
    # 获取所有订单
    sql = ''' select "id","time_start","time_end","states_id_start","states_id_end","drivers_id"
    from orders; '''
    df_orders = pd.read_sql(sql=sql, con=db.engine, index_col='id')
    # 遍历订单
    for order_index, df_order in df_orders.iterrows():
        # 遍历时间段
        for time_index, df_time in df_times.iterrows():
            # 时间戳转时间
            order_time_start = datetime.datetime.time(df_order['time_start'])
            order_time_end = datetime.datetime.time(df_order['time_end'])
            # 订单起点时间是否在当前时间段内
            if df_time['time_start'] <= order_time_start <= df_time['time_end']:
                driver_service = DriversServiceTable(
                    df_time['time_start'], df_time['time_end'],
                    df_order['states_id_start'], df_order['drivers_id'])
                db.session.add(driver_service)
            # 订单终点时间是否在当前时间段内
            elif df_time['time_start'] <= order_time_end <= df_time['time_end']:
                driver_service = DriversServiceTable(df_time['time_start'],
                                                     df_time['time_end'],
                                                     df_order['states_id_end'],
                                                     df_order['drivers_id'])
                db.session.add(driver_service)
            # 当前时间段是否在起点和终点时间段内
            elif df_time['time_start'] >= order_time_start and df_time[
                    'time_end'] <= order_time_end:
                # 确定位置
                states_id = utils.random_choice_state(
                    df_order['states_id_start'], df_order['states_id_end'])
                driver_service = DriversServiceTable(df_time['time_start'],
                                                     df_time['time_end'],
                                                     states_id,
                                                     df_order['drivers_id'])
                db.session.add(driver_service)
        print(i, '/', len(df_orders))
        i += 1
    # 提交数据库会话
    db.session.commit()
    return '司机服务表插入成功！'

@app.route('/insert/<order_statistic_tablename> <order_tablename>')
def insert_order_statistic(order_statistic_tablename,order_tablename):
    """
    功能：每小时统计订单数、订单收益
    输入：统计表名，订单表名
    """
    for i in range(24):
        sql = f'''
        insert into {order_statistic_tablename} select {i},count(*),sum("reward") from {order_tablename}
	        where extract(hour from "time_start") = {i};
        '''
        db.session.execute(sql)
    db.session.commit()
    return '插入成功'